package com.dao;

import com.pojo.Student;
import com.utils.Myutils;
import com.utils.Toolutils;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class Dao {
    /**
     * 添加学生
     * @param s
     * @return
     */
    public int insertStudent(Student s) {
        String sql="insert into student values(?,?,?,?,?,?) ";
        Object[] arr={null,s.getSname(),s.getSex(),s.getSbir(),s.getHobby(),s.getPhoto()};
        try {
            return Myutils.qr.update(sql,arr);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    /**
     * 通过id 修改
     * @param s
     * @return
     */
    public int updateStudentBysid(Student s) {
        String sql="update student set sname=?,sex=?,hobby=?,sbir=?,photo=? where sid=?";
        Object[] param={s.getSname(),s.getSex(),s.getHobby(),s.getSbir(),s.getPhoto(),s.getSid()};
        try {
            return Myutils.qr.update(sql,param);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    /**
     * 通过sid 查学生
     * @param sid
     * @return
     */
    public Student queryStudentBySid(int sid) {
        String sql="select * from student where sid=?";
        try {
            return Myutils.qr.query(sql,new BeanHandler<>(Student.class),sid);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 批量删除
     * @param sid
     * @return
     */
    public int deleteStudent(String sid) {
        String s = sid.substring(1);
        System.out.println(s);
        String sql="delete from student where sid in ("+s+")";
        try {
            return Myutils.qr.update(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    /**
     * 删除
     * @param sid
     * @return
     */
    public int deleteStudentBySid(String sid) {
        String sql="delete from student where sid=?";
        try {
            return Myutils.qr.update(sql,sid);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    /**
     * 模糊分页查询 总条数
     * @param sname
     * @param sex
     * @return
     */
    public int dimQueryStudent(String sname, String sex) {
        String sql="select count(*) from student where 1=1 ";
        if (!"".equals(sname)  && sname!=null){
            sql +=" and sname like '%" + sname + "%'";
        }
        if (!"".equals(sex) && sex!=null){
            sql +=" and sex= '" + sex + "'";
        }
        try {
            long l = (long) Myutils.qr.query(sql, new ScalarHandler());
            return (int)l;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    /**
     * 模糊搜索
     * @param sname
     * @param sex
     * @param tl
     * @return
     */
    public List souSuoStudent(String sname, String sex, Toolutils tl) {
        /*
         * 情况
         * 1、没有传递sname，没有传sex
         * select * from student
         * 2、传递了sname，没有传递sex
         * select * from student where sname like '%sname%'
         * 3、没有传递sname，传递了sex
         * select * from student where sex = sex
         * 4、传递了sname，传递了sex
         * select * from student where sname like '%sname%' and sex = sex
         */
        String sql = "select * from student where 1 = 1 ";

        //传递了sname
        if(sname != null && !"".equals(sname)) {
            sql += " and  sname like '%" + sname + "%'";
        }

        //传递了sex
        if(sex != null && !"".equals(sex)) {
            sql += "  and sex = '" + sex + "'";
        }

        sql += "  limit ?,?";
        try {
            return Myutils.qr.query(sql,new BeanListHandler<>(Student.class),
                    tl.getIndex(),tl.getPagecount());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;

    }
}
